/* GWTask.java
 *
 * Project/Product: JZCG-SHBUS
 *
 * Modify Information:
 * =============================================================================
 *   Author	Date       Description
 *   ----------	---------- ---------------------------------------------------
 *   Administrator	2008-2-25	First release
 *
 * Licensed Materials - Property of APEP
 * =============================================================================
 *    (C) COPYRIGHT Shanghai Auto Mart E-Procurement Co., Ltd. 2007 - 2008
 *    All Rights Reserved.
 *
 *    This software is the confidential and proprietary information of
 *    Shanghai Auto Mart E-Procurement Co., Ltd. ("Confidential Information").
 *    You shall not disclose such Confidential Information and shall use it only
 *    in accordance with the terms of the license agreement you entered into 
 *    with Shanghai Auto Mart E-Procurement Co., Ltd.
 *
 * Noties:
 * =============================================================================
 * 
 */

package com.hitrust.gs.autotask;

import java.io.File;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.hitrust.gs.bean.GSManufactureBean;
import com.hitrust.gs.bean.GSManufactureCategoryBean;
import com.hitrust.gs.bean.GSProviderBean;
import com.hitrust.gs.dao.GSManufactureCategoryDAO;
import com.hitrust.gs.lib.GSSystemConfigurator;
import com.hitrust.gs.lib.db.GSBaseDAO;
import com.hitrust.gs.util.date.DateUtil;
import com.hitrust.gs.util.exception.GSException;

public class GWTask  extends GSBaseDAO{
    //private String path = "./Web root/v3/back/down/";
    //"D:\\eclipse\\workspace\\APEP3\\Web root\\v3\\back\\down\\"
    private String path = GSSystemConfigurator.getParam("gs.GWDataDown.savepath");
    String strFilePath = new File(".").getAbsolutePath();
    
    private String fileName = "";
    private String createTime = "";
    private String errorMsg = "";
    
    public GWTask()
    {
    }
    
    
    //产品分类
    private void writeManufactureCatagory()
    {
        try
        {
            //System.out.println("real path " + strFilePath );
            String strFileDate = DateUtil.getNextUpDay(-1);
            String strDBDate = DateUtil.formateDateTime(strFileDate); 
            //createTime = de.getFullString();
            
            HSSFWorkbook tWb = new HSSFWorkbook();
            HSSFSheet tSheet = tWb.createSheet("ManufactureCategory");
            HSSFRow tRow = null;        
            
            tRow = tSheet.createRow(0);
            int column = 0;
            
            HSSFCell c1 = createCell(tWb,tRow,(short)0,"ManufactureCategoryId");
            HSSFCell c2 = createCell(tWb,tRow,(short)1,"ManufactureCategoryName");
            HSSFCell c3 = createCell(tWb,tRow,(short)2,"ManufactureDesc");
            HSSFCell c4 = createCell(tWb,tRow,(short)3,"ManufactureLevel");
            HSSFCell c5 = createCell(tWb,tRow,(short)4,"UpperManufactureCategory");
            HSSFCell c6 = createCell(tWb,tRow,(short)5,"IsTradeflatManufCategory");
            HSSFCell c7 = createCell(tWb,tRow,(short)6,"ManufactureCategoryId1");
            HSSFCell c8 = createCell(tWb,tRow,(short)7,"ManufactureCategoryId2");
            
            GSManufactureCategoryBean tGSManufactureCategoryBean = new GSManufactureCategoryBean();
            ArrayList tList = manufactureCategoryQuery(strDBDate);
            
            short index = 0;
            if(tList.size()>0){
                for(int i=0;i<tList.size();i++){
                    tRow = tSheet.createRow((short)(index+1));
                    
                    tGSManufactureCategoryBean = (GSManufactureCategoryBean)tList.get(i);
                    
                    createCell(tWb,tRow,(short)0,tGSManufactureCategoryBean.getGs_manufacture_category_id());
                    createCell(tWb,tRow,(short)1,tGSManufactureCategoryBean.getGs_manufacture_category_name());
                    createCell(tWb,tRow,(short)2,tGSManufactureCategoryBean.getGs_manufacture_desc());
                    createCell(tWb,tRow,(short)3,tGSManufactureCategoryBean.getGs_manufacture_level());
                    createCell(tWb,tRow,(short)4,tGSManufactureCategoryBean.getGs_upper_manufacture_category());
                    createCell(tWb,tRow,(short)5,tGSManufactureCategoryBean.getGs_is_tradeflat_manuf_category());
                    createCell(tWb,tRow,(short)6,tGSManufactureCategoryBean.getGs_manufacture_category_id1());
                    createCell(tWb,tRow,(short)7,tGSManufactureCategoryBean.getGs_manufacture_category_id2());                    
                    index++;
                }
            }
            
            fileName = "GW_ManufactureCategory_" + strFileDate + ".xls";
                
            FileOutputStream fileOut = new FileOutputStream(path+"/"+fileName);
            tWb.write(fileOut);
            fileOut.close();
        }
        catch(Exception e)
        {
            errorMsg = e.getMessage();
        }
        
        //保存执行日志

        
    }
   
    private ArrayList manufactureCategoryQuery(String lastUpdateTime){
        //自动同步任务表名：GS_GWTASK(VARCHAR TABLE_NAME,VARCHAR DATA_ID,VARCHAR UPDATE_TIME)
        String tSql = "select b.* " +
        		" from gs_gwtask a,gs_manufacture_category b where a.data_id = b.gs_manufacture_category_id " +
        		" and a.table_name = 'GS_MANUFACTURE_CATEGORY'" +
        		" and a.update_time >='" + lastUpdateTime + "'"
        		;
                
        
        GSManufactureCategoryBean tGSManufactureCategoryBean = null;
        
        ArrayList tList = new ArrayList();
        try{
            
            tList = this.executeQuery(new GSManufactureCategoryBean(),tSql,null);
            
        }catch(Exception e){
            errorMsg = e.getMessage();
        }
        
        return tList;
    }

    //产品
    private void writeManufacture()
    {
        try
        {
            String strFileDate = DateUtil.getNextUpDay(-1);
            String strDBDate = DateUtil.formateDateTime(strFileDate); 
            
            HSSFWorkbook tWb = new HSSFWorkbook();
            HSSFSheet tSheet = tWb.createSheet("Product");
            HSSFRow tRow = null;        
            
            tRow = tSheet.createRow(0);
            int column = 0;
            
            HSSFCell c1 = createCell(tWb,tRow,(short)0,"ManufactureId");
            HSSFCell c2 = createCell(tWb,tRow,(short)1,"ConcentricityCode");
            HSSFCell c3 = createCell(tWb,tRow,(short)2,"ManufactureName");
            HSSFCell c4 = createCell(tWb,tRow,(short)3,"ManufactureCategoryId");
            HSSFCell c5 = createCell(tWb,tRow,(short)4,"ManufactureBrand");
            HSSFCell c6 = createCell(tWb,tRow,(short)5,"ManufactureType");
            HSSFCell c7 = createCell(tWb,tRow,(short)6,"ManufactureSpec");
            HSSFCell c8 = createCell(tWb,tRow,(short)7,"ManufactureUnitprice");
            HSSFCell c9 = createCell(tWb,tRow,(short)8,"QuantUnit");
            HSSFCell c10 = createCell(tWb,tRow,(short)9,"QualityAssurePeriod");
            HSSFCell c11 = createCell(tWb,tRow,(short)10,"ManufacturerName");
            HSSFCell c12 = createCell(tWb,tRow,(short)11,"MakeArea");
            HSSFCell c13 = createCell(tWb,tRow,(short)12,"ProviderId");
            HSSFCell c14 = createCell(tWb,tRow,(short)13,"ManufactureImage");
            HSSFCell c15 = createCell(tWb,tRow,(short)14,"RecentAdjustpriceDate");
            HSSFCell c16 = createCell(tWb,tRow,(short)15,"RecentAdjustpricePrice");
            HSSFCell c17 = createCell(tWb,tRow,(short)16,"AppenderProperties");
            HSSFCell c18 = createCell(tWb,tRow,(short)17,"Remark");
            HSSFCell c19 = createCell(tWb,tRow,(short)18,"IsTradeflatManufacture");
            HSSFCell c20 = createCell(tWb,tRow,(short)19,"IsBuy");
            HSSFCell c21 = createCell(tWb,tRow,(short)20,"Validity");
            
            GSManufactureBean tGSManufactureBean = new GSManufactureBean();
            ArrayList tList = manufactureQuery(strDBDate);
            
            short index = 0;
            if(tList.size()>0){
                for(int i=0;i<tList.size();i++){
                    tRow = tSheet.createRow((short)(index+1));
                    
                    tGSManufactureBean = (GSManufactureBean)tList.get(i);
                    
                    createCell(tWb,tRow,(short)0,tGSManufactureBean.getGs_manufacture_id());
                    createCell(tWb,tRow,(short)1,tGSManufactureBean.getGs_concentricity());
                    createCell(tWb,tRow,(short)2,tGSManufactureBean.getGs_manufacture_name());
                    createCell(tWb,tRow,(short)3,tGSManufactureBean.getGs_manufacture_category_id());
                    createCell(tWb,tRow,(short)4,tGSManufactureBean.getGs_manufacture_brand());
                    createCell(tWb,tRow,(short)5,tGSManufactureBean.getGs_manufacture_type());
                    createCell(tWb,tRow,(short)6,tGSManufactureBean.getGs_manufacture_spec());
                    createCell(tWb,tRow,(short)7,String.valueOf(tGSManufactureBean.getGs_manufacture_unitprice()));
                    createCell(tWb,tRow,(short)8,tGSManufactureBean.getGs_quant_unit());
                    createCell(tWb,tRow,(short)9,tGSManufactureBean.getGs_quality_assure_period());
                    createCell(tWb,tRow,(short)10,tGSManufactureBean.getGs_manufacturer_name());
                    createCell(tWb,tRow,(short)11,tGSManufactureBean.getGs_make_area());
                    createCell(tWb,tRow,(short)12,tGSManufactureBean.getGs_provider_id());
                    createCell(tWb,tRow,(short)13,tGSManufactureBean.getGs_manufacture_images());
                    createCell(tWb,tRow,(short)14,tGSManufactureBean.getGs_recent_adjustprice_date());
                    createCell(tWb,tRow,(short)15,String.valueOf(tGSManufactureBean.getGs_recent_adjusted_price()));
                    createCell(tWb,tRow,(short)16,tGSManufactureBean.getGs_appender_property());
                    createCell(tWb,tRow,(short)17,tGSManufactureBean.getGs_remark());
                    createCell(tWb,tRow,(short)18,tGSManufactureBean.getGs_is_tradeflat_manufacture());
                    createCell(tWb,tRow,(short)19,tGSManufactureBean.getIs_buy());
                    createCell(tWb,tRow,(short)20,tGSManufactureBean.getGs_validity());
                    index++;
                }
            }
            
            fileName = "GW_Manufacture_"+strFileDate+".xls";
                
            FileOutputStream fileOut = new FileOutputStream(path+"/"+fileName);
            tWb.write(fileOut);
            fileOut.close();
        }
        catch(Exception e)
        {
            errorMsg = e.getMessage();
        }
        
        //保存执行日志
   
    }   
    
    private ArrayList manufactureQuery(String lastUpdateTime){
        //自动同步任务表名：GS_GWTASK(VARCHAR TABLE_NAME,VARCHAR DATA_ID,VARCHAR UPDATE_TIME)
        String tSql = "select b.* " +
                "from gs_gwtask a,gs_manufacture b where a.data_id = b.gs_manufacture_id " +
                "and a.table_name = 'GS_MANUFACTURE'" +
                " and a.update_time >='" + lastUpdateTime + "'"
                ;
        
        GSManufactureBean tGSManufactureBean = null;
        
        ArrayList tList = new ArrayList();
        try{
            
            tList = this.executeQuery(new GSManufactureBean(),tSql,null);
            
        }catch(Exception e){
            errorMsg = e.getMessage();
        }
        
        return tList;
    }    


    //供应商
    private void writeProvider()
    {
        try
        {
            String strFileDate = DateUtil.getNextUpDay(-1);
            String strDBDate = DateUtil.formateDateTime(strFileDate); 
            
            HSSFWorkbook tWb = new HSSFWorkbook();
            HSSFSheet tSheet = tWb.createSheet("Provider");
            HSSFRow tRow = null;        
            
            tRow = tSheet.createRow(0);
            int column = 0;
            
            HSSFCell c1 = createCell(tWb,tRow,(short)0,"ProviderId");
            HSSFCell c2 = createCell(tWb,tRow,(short)1,"ProviderShortname");
            HSSFCell c3 = createCell(tWb,tRow,(short)2,"ProviderName");
            HSSFCell c4 = createCell(tWb,tRow,(short)3,"ProviderEngname");
            HSSFCell c5 = createCell(tWb,tRow,(short)4,"CorpProperty");
            HSSFCell c6 = createCell(tWb,tRow,(short)5,"CorpAddr");
            HSSFCell c7 = createCell(tWb,tRow,(short)6,"CorpPost");
            HSSFCell c8 = createCell(tWb,tRow,(short)7,"PrincipalId");
            HSSFCell c9 = createCell(tWb,tRow,(short)8,"PrincipalPhone");
            HSSFCell c10 = createCell(tWb,tRow,(short)9,"ContactId");
            HSSFCell c11 = createCell(tWb,tRow,(short)10,"ContactPhone");
            HSSFCell c12 = createCell(tWb,tRow,(short)11,"ResideCountry");
            HSSFCell c13 = createCell(tWb,tRow,(short)12,"ResideProvince");
            HSSFCell c14 = createCell(tWb,tRow,(short)13,"ResideCity");
            HSSFCell c15 = createCell(tWb,tRow,(short)14,"WorkTrade");
            HSSFCell c16 = createCell(tWb,tRow,(short)15,"WorkManufacture");
            HSSFCell c17 = createCell(tWb,tRow,(short)16,"WorkScope");
            HSSFCell c18 = createCell(tWb,tRow,(short)17,"IsTradeflatProvider");
            HSSFCell c19 = createCell(tWb,tRow,(short)18,"Validity");
            
            GSProviderBean tGSProviderBean = new GSProviderBean();
            ArrayList tList = providerQuery(strDBDate);
            
            short index = 0;
            if(tList.size()>0){
                for(int i=0;i<tList.size();i++){
                    tRow = tSheet.createRow((short)(index+1));
                    
                    tGSProviderBean = (GSProviderBean)tList.get(i);
                    
                    createCell(tWb,tRow,(short)0,tGSProviderBean.getGs_provider_id());
                    createCell(tWb,tRow,(short)1,tGSProviderBean.getGs_provider_shortname());
                    createCell(tWb,tRow,(short)2,tGSProviderBean.getGs_provider_name());
                    createCell(tWb,tRow,(short)3,tGSProviderBean.getGs_provider_engname());
                    createCell(tWb,tRow,(short)4,tGSProviderBean.getGs_corp_property());
                    createCell(tWb,tRow,(short)5,tGSProviderBean.getGs_corp_addr());
                    createCell(tWb,tRow,(short)6,tGSProviderBean.getGs_corp_post());
                    createCell(tWb,tRow,(short)7,tGSProviderBean.getGs_principal_id());
                    createCell(tWb,tRow,(short)8,tGSProviderBean.getGs_principal_phone());
                    createCell(tWb,tRow,(short)9,tGSProviderBean.getGs_contact_id());
                    createCell(tWb,tRow,(short)10,tGSProviderBean.getGs_contact_phone());
                    createCell(tWb,tRow,(short)11,tGSProviderBean.getGs_reside_country());
                    createCell(tWb,tRow,(short)13,tGSProviderBean.getGs_reside_province());
                    createCell(tWb,tRow,(short)12,tGSProviderBean.getGs_reside_city());
                    createCell(tWb,tRow,(short)14,tGSProviderBean.getGs_work_trade());
                    createCell(tWb,tRow,(short)15,tGSProviderBean.getGs_work_manufacture());
                    createCell(tWb,tRow,(short)16,tGSProviderBean.getGs_work_scope());
                    createCell(tWb,tRow,(short)17,tGSProviderBean.getGs_is_tradeflat_provider());
                    createCell(tWb,tRow,(short)18,tGSProviderBean.getGs_validity());
                    index++;
                }
            }
            
            fileName = "GW_Provider_"+strFileDate+".xls";
                
            FileOutputStream fileOut = new FileOutputStream(path+"/"+fileName);
            tWb.write(fileOut);
            fileOut.close();
        }
        catch(Exception e)
        {
            errorMsg = e.getMessage();
        }
        
        //保存执行日志
   
    }   

    private ArrayList providerQuery(String lastUpdateTime){
        //自动同步任务表名：GS_GWTASK(VARCHAR TABLE_NAME,VARCHAR DATA_ID,VARCHAR UPDATE_TIME)
        String tSql = "select b.* " +
                "from gs_gwtask a,gs_provider b where a.data_id = b.gs_provider_id " +
                "and a.table_name = 'GS_PROVIDER'" +
                " and a.update_time >='" + lastUpdateTime + "'"
                ;
        
        GSProviderBean tGSProviderBean = null;
        
        ArrayList tList = new ArrayList();
        try{
            
            tList = this.executeQuery(new GSProviderBean(),tSql,null);
            
        }catch(Exception e){
            errorMsg = e.getMessage();
        }
        
        return tList;
    }        
    
    public void execute()
    {
        try
        {
            //生成产品分类
            writeManufactureCatagory();
            
            // 生成产品
            writeManufacture();

            //生成供应商
            writeProvider();

        }
        catch (Exception e)
        {
            System.out.println(e.getMessage());
        }
    }
  
 
    private HSSFCell createCell(HSSFWorkbook aWb, HSSFRow aRow, short column,String aValue){
        HSSFCell tCell = aRow.createCell(column);
        tCell.setEncoding(HSSFCell.ENCODING_UTF_16);
        tCell.setCellValue(aValue);
        return tCell;
    }    
    
}
